﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace HRForm
{
    public class ReadWriteExcel
    {
        private static Excel.Application xlsApp = null;
        private static Excel.Workbook workbook = null;
        private static Excel.Worksheet worksheet = null;
        /// <summary>
        /// 根据sheet名字获取表单sheet
        /// </summary>
        /// <param name="filename">
        ///     excel数据表的路径
        /// </param>
        /// <param name="sheetname">
        ///     要读取的sheet名字
        /// </param>
        /// <returns>
        ///     返回sheet
        /// </returns>
        public static Excel.Worksheet GetWorksheetByName(string filename, string sheetname)
        {
            try
            {
                xlsApp = new Excel.Application();
                object objOpt = System.Reflection.Missing.Value;
                workbook = xlsApp.Workbooks.Open(filename, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);

                Excel.Sheets sheets = workbook.Worksheets;
                foreach (Excel.Worksheet sheet in sheets)
                {
                    if (sheet.Name == sheetname)
                    {
                        worksheet = sheet;
                        break;
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return worksheet;
        }
        /// <summary>
        /// 数据读取后，关闭excel程序
        /// </summary>
        public static void close()
        {
            xlsApp.Quit();
            xlsApp = null;

        }

        //用查询表的方式查询并show在数据集控件上
        public static DataSet LoadDataFromExcel(string filePath)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM  [Sheet5$]";//更改Sheet名称，比如sheet2，等等 

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, "Sheet5");
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }

        //用查询表的方式查询并show在数据集控件上
        public static DataSet LoadDataFromExcel(string filePath,string sheetname)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM  ["+sheetname+"$]";//更改Sheet名称，比如sheet2，等等 

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, sheetname);
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message, "提示信息",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }


        public static void WriteToExcel(string filename)
        {

        }

        /// </summary>
        /// <param name="mydgv">控件 DataGridView</param>
        /// <param name="dic">中英文对照的标题</param>
        public static void ExportTasks(DataGridView mydgv, Dictionary<string, string> dic)
        {
            // 定义要使用的Excel 组件接口
            // 定义Application 对象,此对象表示整个Excel 程序
            Microsoft.Office.Interop.Excel.Application excelApp = null;
            // 定义Workbook对象,此对象代表工作薄
            Microsoft.Office.Interop.Excel.Workbook workBook;
            // 定义Worksheet 对象,此对象表示Execel 中的一张工作表
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            //定义Range对象,此对象代表单元格区域
            Microsoft.Office.Interop.Excel.Range range;

            int dcell = 1;
            int rowindex = 0; int colindex = 0;

            int rowcount = mydgv.Rows.Count;
            int colcount = mydgv.Columns.Count;
            int dispcolcount = dic.Count;
            try
            {
                //初始化 Application 对象 excelApp
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                //在工作薄的第一个工作表上创建任务列表
                workBook = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                ws = (Excel.Worksheet)workBook.Worksheets[1];

                // 命名工作表的名称为
                ws.Name = "Sheet1";
                //创建缓存
                Object[,] objdata = new object[rowcount + 1, colcount];
                //创建标题
                foreach (string s in dic.Keys)
                {
                    objdata[rowindex, colindex++] = dic[s].ToString();
                }
                //获取数据
                for (int i = 0; i < rowcount; i++)
                {
                    dcell = 0;
                    foreach (string ss in dic.Keys)
                    {
                        for (int j = 0; j < colcount; j++)
                        {
                            if (mydgv.Columns[j].Name == ss)
                            {
                                objdata[i + 1, dcell++] = mydgv.Rows[i].Cells[j].FormattedValue.ToString(); //得到样式之后的值
                            }
                        }
                    }
                }
                //写入Excel   
                range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[rowcount, dispcolcount]);
                range.Value2 = objdata;
                System.Windows.Forms.Application.DoEvents();
                //设置格式
                excelApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; //全局左对齐
                excelApp.Cells.EntireColumn.AutoFit();
                range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[1, colcount]);
                range.Font.Bold = true; //标题粗体
                //显示 Excel
                excelApp.Visible = true;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
